﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Windows.Interop;



using Npgsql;

namespace Mimosa_Monitor
{
    /// <summary>
    /// MainWindow.xaml에 대한 상호 작용 논리
    /// </summary>
    public partial class MainWindow : Window
    {
        const string DBConnectionString = "Server={0};Port={1};User Id={2};Password={3};Database=sipswitch;MaxPoolSize=5;MinPoolSize=0;Timeout=5;Encoding=UNICODE;";
        const string DBPort = "5432";
        const string DBHost = "localhost";
        const string DBUser = "postgres";
        const string DBPassword = "coolnice";

        NpgsqlConnection connPgsql = null;

        enum Operation
        {
            READ,
            ADD,
            MODIFY,
            REMOVE
        }

        class UserInfo
        {
            public string UserID
            {
                get;
                set;
            }

            public string UserName
            {
                get;
                set;
            }

            public string UA_AOR
            {
                get;
                set;
            }

            public string CreateDate
            {
                get;
                set;
            }

            public string ExpiredDate
            {
                get;
                set;
            }

            public string Status
            {
                get;
                set;
            }

            public string UserType
            {
                get;
                set;
            }

            public string ServiceBegin
            {
                get;
                set;
            }

            public string ServiceEnd
            {
                get;
                set;
            }

            public string TelecomVendor
            {
                get;
                set;
            }

            public string Password
            {
                get;
                set;
            }

            public string Phone
            {
                get;
                set;
            }
           
        }

        class Dialog
        {
            public string BeginTime
            {
                get;
                set;
            }

            public string EndTime
            {
                get;
                set;
            }

            public string FromUser
            {
                get;
                set;
            }

            public string ToUser
            {
                get;
                set;
            }

            public string Elapsed
            {
                get;
                set;
            }
        }

        Operation op;

        public MainWindow()
        {
            InitializeComponent();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            try
            {
                DBConnect();
                dgUser.ItemsSource = GetAccountList();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        private void DBConnect()
        {
            if (this.connPgsql != null)
            {
                this.connPgsql.Close();
                this.connPgsql = null;
            }

            //DBConnectionWindow connWindow = new DBConnectionWindow();
            //if (connWindow.ShowDialog() != true)
            //    return;

            string connectionString = string.Format(DBConnectionString, DBHost, DBPort, DBUser, DBPassword);

            try
            {
                connPgsql = new NpgsqlConnection(connectionString);
                connPgsql.Open();

            }
            catch (Exception npgsqlExcp)
            {
                throw new ApplicationException("Exception ExecuteNonQuery (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
            }

        }

        private List<UserInfo> GetAccountList()
        {
            List<UserInfo> users = new List<UserInfo>();
            string query = "select sipaccountid, name, secret, account_type, ua_aor, status, telecom_vendor, svc_begin_date, svc_end_date, phone_number from sipaccounts";
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
                Npgsql.NpgsqlDataReader reader = null;
                try
                {
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        UserInfo user = new UserInfo();
                        users.Add(user);
                        user.UserID = reader[0].ToString();
                        user.UserName = reader[1].ToString();
                        user.Password = reader[2].ToString();
                        user.UserType = reader[3].ToString();
                        user.UA_AOR = reader[4].ToString();
                        user.Status = reader[5].ToString();
                        user.TelecomVendor = reader[6].ToString();
                        user.ServiceBegin = reader[7].ToString();
                        user.ServiceEnd = reader[8].ToString();
                        user.Phone = reader[9].ToString();
                    }
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
            }

            return users;
        }

        private List<UserInfo> GetConnectedAccountList()
        {
            List<UserInfo> users = new List<UserInfo>();
            string query = "select sipaccountid, name,account_type, ua_aor,status from sipaccounts where status > '0000'";
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
                Npgsql.NpgsqlDataReader reader = null;
                try
                {
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        UserInfo user = new UserInfo();
                        users.Add(user);
                        user.UserID = reader[0].ToString();
                        user.UserName = reader[1].ToString();
                        user.UserType = reader[2].ToString();
                        user.UA_AOR = reader[3].ToString();
                        user.Status = reader[4].ToString();
                    }
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
            }

            return users;
        }

        private void TabControl_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            GetData();
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            GetData();
        }


        private void GetData()
        {
            if (connPgsql == null)
                return;

            //this.Dispatcher.BeginInvoke(new Action(() =>
            //this.Blinder.Visibility = System.Windows.Visibility.Visible), System.Windows.Threading.DispatcherPriority.Loaded);
            
            dgUser.ItemsSource = GetAccountList();
            
            //this.Dispatcher.BeginInvoke(new Action(() =>
            //this.Blinder.Visibility = System.Windows.Visibility.Collapsed), System.Windows.Threading.DispatcherPriority.Loaded);
        }

        

        private void Button_Add(object sender, RoutedEventArgs e)
        {
            this.Border1.BorderBrush = Brushes.Red;
            this.tbID.Text = "";
            this.tbName.Text = "";
            this.tbUAID.Text = "";
            this.tbPwd.Text = "";
            this.dpBegin.SelectedDate = System.DateTime.Now;
            this.dpEnd.SelectedDate = System.DateTime.Now.Add(TimeSpan.FromDays(30));
            this.tbID.IsEnabled = true;
            this.tbName.IsEnabled = true;
            this.tbUAID.IsEnabled = true;
            this.tbPwd.IsEnabled = true;


            op = Operation.ADD;
            this.buttonOK.Visibility = System.Windows.Visibility.Visible;

            this.detailTab.SelectedIndex = 0;
        }
        
        private void Button_Modify(object sender, RoutedEventArgs e)
        {
            this.Border1.BorderBrush = Brushes.Red;
            this.tbID.IsEnabled = false;
            this.tbName.IsEnabled = false;
            this.tbUAID.IsEnabled = true;
            this.tbPwd.IsEnabled = true;
            op = Operation.MODIFY;
            this.buttonOK.Visibility = System.Windows.Visibility.Visible;
            this.detailTab.SelectedIndex = 0;
        }

        private void Button_Remove(object sender, RoutedEventArgs e)
        {
            if (this.dgUser.SelectedItem != null)
            {
                UserInfo userInfo = this.dgUser.SelectedItem as UserInfo;
                RemoveUser(userInfo);
            }
        }

        private void dgUser_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (this.dgUser.SelectedItem != null)
            {
                UserInfo userInfo = this.dgUser.SelectedItem as UserInfo;
                this.tbID.Text = userInfo.UserID;
                this.tbName.Text = userInfo.UserName;
                this.tbUAID.Text = userInfo.UA_AOR;
                this.tbPwd.Text = userInfo.Password;
                this.dpBegin.SelectedDate = System.DateTime.ParseExact(userInfo.ServiceBegin, "yyyyMMddhhmmss", System.Globalization.CultureInfo.CurrentCulture);
                this.dpEnd.SelectedDate = System.DateTime.ParseExact(userInfo.ServiceEnd, "yyyyMMddhhmmss", System.Globalization.CultureInfo.CurrentCulture);    
                this.tbID.Text = userInfo.UserID;

                this.buttonOK.Visibility = System.Windows.Visibility.Collapsed;
                this.op = Operation.READ;
                this.Border1.BorderBrush = Brushes.Blue;

                this.dgDialog.ItemsSource = GetDialogList(userInfo);
            }
        }

        private void buttonOK_Click(object sender, RoutedEventArgs e)
        {
            UserInfo userInfo = new UserInfo();
            userInfo.UserID = this.tbID.Text;
            userInfo.UserName = this.tbName.Text;
            userInfo.UserType = "0001";
            userInfo.UA_AOR = this.tbUAID.Text;
            userInfo.ServiceBegin = dpBegin.SelectedDate.Value.ToString("yyyyMMddhhmmss");
            userInfo.ServiceEnd = dpEnd.SelectedDate.Value.ToString("yyyyMMddhhmmss");
            userInfo.Password = this.tbPwd.Text;
            switch (op)
            {
                case Operation.ADD:
                    {
                        userInfo.Status = "0000";


                        if (userInfo.UserID == string.Empty || userInfo.UserName == string.Empty || userInfo.Password == string.Empty || userInfo.UA_AOR == string.Empty)
                        {
                            MessageBox.Show("비어있는 정보를 입력해주세요.");
                            return;
                        }
                        AddUser(userInfo);
                    }
                    break;
                case Operation.MODIFY:
                    {
                        if (userInfo.UserID == string.Empty || userInfo.UserName == string.Empty || userInfo.Password == string.Empty || userInfo.UA_AOR == string.Empty)
                        {
                            MessageBox.Show("비어있는 정보를 입력해주세요.");
                            return;
                        }
                        ModifyUser(userInfo);
                    }
                    break;
            }
            this.Border1.BorderBrush = Brushes.Blue;
        }
        private void ModifyUser(UserInfo userInfo)
        {
            List<UserInfo> users = new List<UserInfo>();
            string query = string.Format(@"update sipaccounts set secret = '{0}'
                                                   , account_type = '{1}'
                                                   , ua_aor = '{2}'
                                                   , status = '{3}'
                                                   , telecom_vendor = '{4}'
                                                   , svc_begin_date = '{5}'
                                                   , svc_end_date = '{6}'
                                                   , phone_number = '{7}'
                            where sipaccountid = '{8}'"
                                             , userInfo.Password
                                             , userInfo.UserType
                                             , userInfo.UA_AOR
                                             , userInfo.Status
                                             , userInfo.TelecomVendor
                                             , userInfo.ServiceBegin
                                             , userInfo.ServiceEnd
                                             , userInfo.Phone
                                             , userInfo.UserID);

            bool result = true;
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                    result = false;
                }
            }
            if (result)
            {
                this.buttonOK.Visibility = System.Windows.Visibility.Collapsed;
                op = Operation.READ;

                GetData();
            }
        }

        private void AddUser(UserInfo userInfo)
        {
            List<UserInfo> users = new List<UserInfo>();
            string query = string.Format(@"insert into  sipaccounts (sipaccountid, name, secret, account_type, ua_aor, status, telecom_vendor, svc_begin_date, svc_end_date, phone_number)
                                               values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}', {9})"
                                             , userInfo.UserID
                                             , userInfo.UserName
                                             , userInfo.Password
                                             , userInfo.UserType
                                             , userInfo.UA_AOR
                                             , userInfo.Status
                                             , userInfo.TelecomVendor
                                             , userInfo.ServiceBegin
                                             , userInfo.ServiceEnd
                                             , userInfo.Phone);
            bool result = true;
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                    result = false;
                }

                if (result)
                {
                    this.buttonOK.Visibility = System.Windows.Visibility.Collapsed;
                    op = Operation.READ;

                    GetData();
                }
               
            }
        }

        private void RemoveUser(UserInfo userInfo)
        {
            List<UserInfo> users = new List<UserInfo>();
            string query = string.Format(@"delete from sipaccounts
                            where sipaccountid = '{0}'", userInfo.UserID);

            bool result = true;
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                    result = false;
                }
            }
            if (result)
            {
                this.buttonOK.Visibility = System.Windows.Visibility.Collapsed;
                op = Operation.READ;

                GetData();
            }
        }

        private List<Dialog> GetDialogList(UserInfo userInfo)
        {
            List<Dialog> dialogs = new List<Dialog>();
            string query = string.Format("select to_timestamp(begin_time,'yyyymmddhh24miss'), to_timestamp(end_time,'yyyymmddhh24miss'), from_user, to_user , to_timestamp(end_time,'yyyymmddhh24miss') - to_timestamp(begin_time,'yyyymmddhh24miss') from dialog where from_user = '{0}'", userInfo.UserID); 
            using (NpgsqlCommand command = new NpgsqlCommand(query, connPgsql))
            {
            //    command.Parameters.Add("from_user", userInfo.UserID);
                Npgsql.NpgsqlDataReader reader = null;
                try
                {
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Dialog dialog = new Dialog();
                        dialogs.Add(dialog);
                        dialog.BeginTime = reader[0].ToString();
                        dialog.EndTime = reader[1].ToString();
                        dialog.FromUser = reader[2].ToString();
                        dialog.ToUser = reader[3].ToString();
                        dialog.Elapsed = reader[4].ToString();
                    }
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
            }

            return dialogs;
        }



        #region WM_COPYDATA
        public const Int32 WM_COPYDATA = 0x004a;
        protected override void OnSourceInitialized(EventArgs e)
        {
            HwndSource source = PresentationSource.FromVisual(this) as HwndSource;
            if (source != null)
            {
                source.AddHook(new HwndSourceHook(WinProc));
            }
        }

        public static IntPtr WinProc(IntPtr hwnd, int msg, IntPtr wParam, IntPtr lParam, ref bool handled)
        {
            switch (msg)
            {
                case WM_COPYDATA:
                    {
                        // Do something interesting here.
                        break;
                    }
            }
            return IntPtr.Zero;
        }
        #endregion
    }
}
